package com.hp.util;

import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class DBHelper {
    private static DBHelper myHepler = new DBHelper();
    private DBHelper(){

    }
    public static DBHelper getMyHepler(){
        return myHepler;
    }
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/item?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
    private static String username = "root";
    private static String password = "root";
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;
    static {
        try {
            Class.forName(driver);
            System.out.println("驱动加载成功！！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() {
        try {
            conn = DriverManager.getConnection(url, username, password);
            System.out.println("数据库加载成功！！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 封装查询方法
     * @param sql
     * @param objs
     * @return
     */
    public List<Map> select(String sql,Object...objs){
        try {
            //加载驱动
            conn = getConn();
            ps = conn.prepareStatement(sql);
            System.out.println("查询方法:"+sql);
            for (int i = 0; i < objs.length; i++) {
                Object obj = objs[i];
                System.out.println("第"+(i+1)+"个参数:"+obj);
                ps.setObject(i+1,obj);
            }
            rs = ps.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            List<Map> list = new ArrayList<>();
            int index = md.getColumnCount();
            while (rs.next()){
                Map map = new HashMap();
                for (int i = 1; i <= index; i++) {
                    String key = md.getColumnLabel(i);
                    map.put(key,rs.getObject(key));
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return null;
    }

    /**
     * 查询单个
     * @param sql
     * @param id
     * @return
     */
    public Map showById(String sql,Object...id){
        try {
            //加载驱动
            conn = getConn();
            ps = conn.prepareStatement(sql);
            System.out.println("查询方法:"+sql);
            for (int i = 0; i < id.length; i++) {
                Object o = id[i];
                System.out.println("第"+(i+1)+"个参数:"+o);
                ps.setObject(i+1,o);
            }
            rs = ps.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int index = md.getColumnCount();
            Map map = new HashMap();
            if (rs.next()){
                for (int i = 1; i <= index; i++) {
                    String key = md.getColumnLabel(i);
                    map.put(key, rs.getObject(key));
                }
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return null;
    }

    /**
     * 修改 删除 添加
     * @param sql
     * @param objs
     * @return
     */
    public int update(String sql,Object...objs){
        int is =0;
        try {
            //加载驱动
            conn = getConn();
            ps = conn.prepareStatement(sql);
            System.out.println("查询方法:"+sql);
            for (int i = 0; i < objs.length; i++) {
                Object obj = objs[i];
                System.out.println("第"+(i+1)+"个参数:"+obj);
                ps.setObject(i+1,obj);
            }
            is = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return is;
    }

    /**
     * 分页
     * @param page
     * @param limit
     * @param sql
     * @param obj
     * @return
     */
    public PageBean<Map> pageList(int page, int limit, String sql, Object...obj){
        System.out.println(page+limit);
        //获取总条数
        int count = getCount(sql,obj);
        System.out.println(count);
        //根据总条数和每页条数进行计算总页数(根据是否有余数，判断是否需要页数加一)
        int totalPages = count % limit == 0 ? count/limit : (count/limit)+1;
        //判断当前页是否大于总页数或小于1页 免得出现负数和不存在的页数
        if(page <= 1){
           page = 1;
        }else {
            page = totalPages;
        }
        //判断通过后计算当前分页的起始位置
        int startindex = (page - 1) * limit;
        System.out.println(startindex+"  "+limit);
        sql+=" limit " + startindex +" , " + limit;
        System.out.println("分页方法:"+sql);
        List<Map> list = select(sql,obj);
        System.out.println(list);
        PageBean<Map> pagelist = new PageBean<>(totalPages,count,page,limit,list);
        System.out.println(pagelist);
        return pagelist;
    }
    public int getCount(String sql, Object... obj){
        int count =0;
        conn = getConn();
        try {
            ps = conn.prepareStatement("select count(*) num from ("+sql+") d");
            System.out.println("查询总条数："+sql);
            for (int i = 0; i < obj.length; i++) {
                Object s = obj[i];
                System.out.println("第"+(i+1)+"个参数:"+s);
                ps.setObject(i+1,s);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return count;
    }
    private static void close(){
        try {
            if(conn != null) {
                conn.close();
            }
            if(ps != null) {
                ps.close();
            }
            if(rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        System.out.println(getMyHepler());
    }
}
